<html>

<head>
<title>SQL Relay - Getting Started With Microsoft SQL Server 2000 Desktop Engine</title>
<link rel="stylesheet" href="../css/styles.css">
</head>

<body>

<span class="heading1">Getting Started With Microsoft SQL Server 2000 Desktop Engine</span><br>

<ul>
<li><a href="#installation">Installation</a></li>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
</ul>

<a name="installation"></a>
<span class="heading1">Installation</span><br>

<p>Microsoft SQL Server is expensive commercial software, but you can download
Microsoft SQL Server 2000 Desktop Engine for free from
<a href="http://www.microsoft.com/sql/msde/downloads/download.asp">http://www.microsoft.com/sql/msde/downloads/download.asp</a>.  MSDE is a stripped down
version of SQL Server that's good for testing software that will ultimately run
against SQL Server.</p>

<p>I've successfully installed and used MSDE 2000 A on Windows XP.  I haven't
tried to run it on any other platform.</p>

<p>Read the instructions on the page and download the file to your desktop.  It
should be called something MSDE2000A.exe or something similar.</p>

<p>To unpack MSDE 2000, double-click MSDE2000A.exe.  When prompted with a
license agreement, click "I Agree".  Click "Finish" to unpack the distribution
in C:\MSDERelA.  If prompted with "The specified output folder does not exist.  
Create it?"  Click "Yes".  Extraction will proceed and a progress bar will
keep you informed of the extraction progress.  Ultimately a dialog will appear
indicating that "The packages have been delivered successfully."  Click OK.</p>

<p>To install an instance of MSDE 2000, open a command prompt and type:</p>

<blockquote>
<pre>
cd C:\MSDERelA
setup sapwd="sapassword" disablenetworkprotocols=0 securitymode=sql
</pre>
</blockquote>

<p>This command creates an instance of MSDE with an administrative user named
<i>sa</i> with password <i>sapassword</i>.  The disablenetworkprotocols=0 switch
configures the instance to allow connections from remote hosts on tcp port 1433.
The securitymode=sql switch configures the instance to use "Mixed Mode" which
allows users to log in using Windows or SQL Authentication modes.</p>

<p>After a reboot, MSDE should be up and running.</p>

<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>

<p>Log in using the following command.  When it promts you for a password,
type <i>sapassword</i>.</p>

<blockquote>
<b>osql -U sa -S localhost</b>
</blockquote>

<p>Though you could create your own tables using the <i>sa</i> user and the 
system database, it's not a good idea.  You should create a user database.
The following commands create a database named <i>testdb</i> and switch
the current database to <i>testdb</i>.</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>create</FONT></B> database testdb
go
<B><FONT color=#a62828>use</FONT></B> testdb
go
</PRE>
</blockquote>

<p>Creating a user is a 2 step process.  First a universal login must be 
created, then it must be added as a user to a particular database.  
Execute the following stored procedures and queries to create a login 
named <i>testuser</i> with password <i>testpassword</i>, add it as a user of 
<i>testdb</i> and give it all permissions.</p>

<blockquote>
<PRE>
sp_addlogin <FONT color=#ff00ff>&quot;testuser&quot;</FONT>,<FONT color=#ff00ff>&quot;testpassword&quot;</FONT>
go
sp_adduser <FONT color=#ff00ff>&quot;testuser&quot;</FONT>
go
<B><FONT color=#a62828>grant</FONT></B> <B><FONT color=#a62828>all</FONT></B> <FONT color=#6959cf>to</FONT> testuser
go
quit
</PRE>
</blockquote>

<p>Now you can log in as <i>testuser</i> using the following command.  When
promted for a password, enter <i>testpassword</i>.</p>

<blockquote>
<b>osql -U testuser -S localhost -D testdb</b>
</blockquote>

<p>This should be enough to get you started.</p>

<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>

<p>Now, if you log out and log back in, you can access the database using the 
<i>osql</i> client as follows.</p>

<blockquote>
<b>osql -U testuser -S localhost</b>
</blockquote>

<p>Once you're connected, the <i>osql</i> client prompts you to enter a query.
First you should "use" a particular database, then you can run queries.  Queries
may be split across multiple lines.  To run a query, type go on a line by
itself.  To exit, type quit on a line by itself.</p>

<p>A sample <i>isql</i>session follows.</p>

<blockquote>
<PRE>
osql -U testuser -S localhost
Password: 
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>use</FONT></B> testdb
<FONT color=#ff00ff>2</FONT>&gt; go
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
<FONT color=#ff00ff>2</FONT>&gt; col1 <B><FONT color=#288a51>char</FONT></B>(<FONT color=#ff00ff>40</FONT>),
<FONT color=#ff00ff>3</FONT>&gt; col2 <B><FONT color=#288a51>integer</FONT></B>
<FONT color=#ff00ff>4</FONT>&gt; )
<FONT color=#ff00ff>5</FONT>&gt; go
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>select</FONT></B> name <FONT color=#6959cf>from</FONT> sysobjects <FONT color=#6959cf>where</FONT> <FONT color=#6959cf>type</FONT>=<FONT color=#ff00ff>'U'</FONT>
<FONT color=#ff00ff>2</FONT>&gt; go
 name                           
 <FONT color=#0000ff>------------------------------ </FONT>
 testtable                      

(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>)
<FONT color=#ff00ff>2</FONT>&gt; go
(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>)
<FONT color=#ff00ff>2</FONT>&gt; go
(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>)
<FONT color=#ff00ff>2</FONT>&gt; go
(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
<FONT color=#ff00ff>2</FONT>&gt; go
 col1                                     col2        
 <FONT color=#0000ff>---------------------------------------- ----------- </FONT>
 hello                                             <FONT color=#ff00ff>50</FONT> 
 hi                                                <FONT color=#ff00ff>60</FONT> 
 bye                                               <FONT color=#ff00ff>70</FONT> 

(<FONT color=#ff00ff>3</FONT> rows affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>
<FONT color=#ff00ff>2</FONT>&gt; go
(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
<FONT color=#ff00ff>2</FONT>&gt; go
 col1                                     col2        
 <FONT color=#0000ff>---------------------------------------- ----------- </FONT>
 hello                                             <FONT color=#ff00ff>50</FONT> 
 hi                                                 <FONT color=#ff00ff>0</FONT> 
 bye                                               <FONT color=#ff00ff>70</FONT> 

(<FONT color=#ff00ff>3</FONT> rows affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>
<FONT color=#ff00ff>2</FONT>&gt; go
(<FONT color=#ff00ff>1</FONT> row affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
<FONT color=#ff00ff>2</FONT>&gt; go
 col1                                     col2        
 <FONT color=#0000ff>---------------------------------------- ----------- </FONT>
 hi                                                 <FONT color=#ff00ff>0</FONT> 
 bye                                               <FONT color=#ff00ff>70</FONT> 

(<FONT color=#ff00ff>2</FONT> rows affected)
<FONT color=#ff00ff>1</FONT>&gt; <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable
<FONT color=#ff00ff>2</FONT>&gt; go
<FONT color=#ff00ff>1</FONT>&gt; quit
</PRE>
</blockquote>

<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>

<p>Accessing MSDE from SQL Relay requires an instance entry in your 
<i>sqlrelay.conf</i> file for the database that you want to access.  Here is an
example <i>sqlrelay.conf</i> which defines an SQL Relay instance called
msdetest.  This instance connects to the <i>testdb</i> database on the server
<i>msde</i> as the user <i>testuser</i> with password <i>testpassword</i>.</p>

<PRE>
<FONT color=#0000ff>&lt;?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>&quot;1.0&quot;</FONT><FONT color=#0000ff>?&gt;</FONT>
<FONT color=#008a8e>&lt;!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>&quot;sqlrelay.dtd&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
<FONT color=#008a8e>&lt;instances&gt;</FONT>

        <FONT color=#008a8e>&lt;instance id=</FONT><FONT color=#ff00ff>&quot;msdetest&quot;</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>&quot;9000&quot;</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>&quot;/tmp/msdetest.socket&quot;</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>&quot;freetds&quot;</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>&quot;3&quot;</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>&quot;0&quot;</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>&quot;60&quot;</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>&quot;commit&quot;</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>&quot;600&quot;</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>&quot;listener&quot;</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>&quot;pass&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
                <FONT color=#008a8e>&lt;users&gt;</FONT>
                        <FONT color=#008a8e>&lt;user user=</FONT><FONT color=#ff00ff>&quot;msdetest&quot;</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>&quot;msdetest&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/users&gt;</FONT>
                <FONT color=#008a8e>&lt;connections&gt;</FONT>
                        <FONT color=#008a8e>&lt;connection connectionid=</FONT><FONT color=#ff00ff>&quot;msdetest&quot;</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>&quot;server=msde;db=testdb;user=testuser;password=testpassword;&quot;</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/connections&gt;</FONT>
        <FONT color=#008a8e>&lt;/instance&gt;</FONT>

<FONT color=#008a8e>&lt;/instances&gt;</FONT>
</PRE>

<p>Note that the <i>server=msde</i> parameter does not refer to a host named
msde.  Rather it refers to an entry in the freetds.conf file.  For example, the
following freetds.conf entry defines a server named <i>msde</i> on the host
<i>remotehost</i> running on port 1433.</p>

<blockquote>
<pre>
[msde]
	host = remotehost
	port = 1433
	tds version = 8.0
</pre>
</blockquote>

<p>Now you can start up this instance with the following command.</p>

<blockquote>
<b>sqlr-start -id msdetest</b>
</blockquote>

<p>Note that there are a couple of common problems people have with the FreeTDS
connection.  See the <a href="../faq.html">FAQ</a> and the
<a href="../running.html#problems">common problems</a> section of the document
on running SQL Relay for more info.</p>

<p>To connect to the instance and run queries, use the following command.</p>

<blockquote>
<b>sqlrsh -id msdetest</b>
</blockquote>

<p>The following command shuts down the SQL Relay instance.</p>

<blockquote>
<b>sqlr-stop msdetest</b>
</blockquote>

</body>

</html>
